﻿Imports SIGI.Common.BE
Imports System.Data.SqlClient
Imports System.Configuration

Public Class UserDAC

#Region "<< -SearchByUserName- >>"
    Public Shared Function SearchByUserName(pUserName As String) As UserBE
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wUserBE As UserBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Usuarios_s_NombreUsuario"

            'Creo los parámetros
            wSqlCmd.Parameters.Add("@NombreUsuario", SqlDbType.NVarChar, 100)

            'Asigno los parámetros
            wSqlCmd.Parameters("@NombreUsuario").Value = pUserName

            'Abro la conexión a la BD
            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            'Leo los resultados de la consulta
            If wReader.Read() Then
                wUserBE = New UserBE

                wUserBE.UserName = Convert.ToString(wReader("NombreUsuario"))
                wUserBE.Password = Convert.ToString(wReader("Password"))
                wUserBE.Name = Convert.ToString(wReader("Nombre"))
                wUserBE.Answer = Convert.ToString(wReader("Respuesta"))
                wUserBE.Blocked = Convert.ToBoolean(wReader("Bloqueado"))
                wUserBE.DocumentNumber = Convert.ToInt32(wReader("NroDocumento"))
                If Not wReader("CodPostal") Is DBNull.Value Then
                    wUserBE.PostalCode = Convert.ToString(wReader("CodPostal"))
                End If
                If Not wReader("Direccion") Is DBNull.Value Then
                    wUserBE.Direction = Convert.ToString(wReader("Direccion"))
                End If
                If Not wReader("Celular") Is DBNull.Value Then
                    wUserBE.Celphone = Convert.ToString(wReader("Celular"))
                End If
                If Not wReader("Telefono") Is DBNull.Value Then
                    wUserBE.Phone = Convert.ToString(wReader("Telefono"))
                End If
                If Not wReader("Email") Is DBNull.Value Then
                    wUserBE.Email = Convert.ToString(wReader("Email"))
                End If
                If Not wReader("BlockStart") Is DBNull.Value Then
                    wUserBE.BlockStarted = Convert.ToDateTime(wReader("BlockStart"))
                End If
                If Not wReader("FechaBaja") Is DBNull.Value Then
                    wUserBE.EndDate = Convert.ToDateTime(wReader("FechaBaja"))
                End If
                If Not wReader("CUIT") Is DBNull.Value Then
                    wUserBE.Cuit = Convert.ToString(wReader("CUIT"))
                End If
                'Tipo de Documento
                wUserBE.DocumentType = New DocumentTypeBE
                wUserBE.DocumentType.Id = Convert.ToInt32(wReader("Tipo_Doc"))
                wUserBE.DocumentType.Description = Convert.ToString(wReader("TipoDocumento"))
                'Perfil
                wUserBE.Profile = New ProfileBE
                wUserBE.Profile.Id = Convert.ToInt32(wReader("Id_Perfil"))
                wUserBE.Profile.Description = Convert.ToString(wReader("Perfil"))
                'Pregunta
                wUserBE.Question = New QuestionBE
                wUserBE.Question.Id = Convert.ToInt32(wReader("ID_Pregunta"))
                wUserBE.Question.Description = Convert.ToString(wReader("Pregunta"))
                'País
                If Not wReader("Id_Pais") Is DBNull.Value Then
                    wUserBE.Country = New CountryBE
                    wUserBE.Country.Id = Convert.ToInt32(wReader("Id_Pais"))
                    wUserBE.Country.Description = Convert.ToString(wReader("Pais"))
                End If
                'Provincia
                If Not wReader("Id_Provincia") Is DBNull.Value Then
                    wUserBE.Province = New ProvinceBE
                    wUserBE.Province.Id = Convert.ToInt32(wReader("Id_Provincia"))
                    wUserBE.Province.Description = Convert.ToString(wReader("Provincia"))
                End If
                'Ciudad
                If Not wReader("Id_Ciudad") Is DBNull.Value Then
                    wUserBE.City = New CityBE
                    wUserBE.City.Id = Convert.ToInt32(wReader("Id_Ciudad"))
                    wUserBE.City.Description = Convert.ToString(wReader("Ciudad"))
                End If
                'Motivo de Baja
                If Not wReader("MotivoBaja") Is DBNull.Value Then
                    wUserBE.AccountCloseMotive = Convert.ToString(wReader("MotivoBaja"))
                End If
                'Sexo
                wUserBE.Sex = New SexBE
                wUserBE.Sex.Id = Convert.ToInt32(wReader("Id_Sexo"))
                wUserBE.Sex.Description = Convert.ToString(wReader("Sexo"))

            End If

            'Cierro la conexión a la BD
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wSqlConn = Nothing
            wSqlCmd = Nothing
            wReader = Nothing
        End Try

        Return wUserBE
    End Function
#End Region

#Region "<< -BlockUser- >>"
    'Bloquea o desbloquea la cuenta de un usuario.
    Public Shared Sub BlockUser(pUserName As String, pBlock As Boolean)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Bloquear_Usuario]"

            'Creo los parámetros
            wSqlCmd.Parameters.Add("@NombreUsuario", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters("@NombreUsuario").Value = pUserName

            wSqlCmd.Parameters.Add("@Bloquear", SqlDbType.Bit)
            wSqlCmd.Parameters("@Bloquear").Value = pBlock

            'Abro la conexión a la BD
            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            'Cierro la conexión a la BD
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wSqlConn = Nothing
            wSqlCmd = Nothing
        End Try
    End Sub
#End Region

#Region "<< -SearchByParams- >>"
    Public Shared Function SearchByParams(pUser As UserBE, pNotActiveUsers As Boolean) As List(Of UserBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty
        Dim wReader As SqlDataReader = Nothing
        Dim wUserBE As UserBE = Nothing
        Dim wUserList As List(Of UserBE) = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Usuarios_s_ByParams"

            wSqlCmd.Parameters.Add("@NombreUsuario", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Id_Perfil", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@NroDocumento", SqlDbType.Decimal)
            wSqlCmd.Parameters.Add("@UsuriosDadosBaja", SqlDbType.Bit)

            If Not String.IsNullOrEmpty(pUser.UserName) Then
                wSqlCmd.Parameters("@NombreUsuario").Value = pUser.UserName
            End If
            If Not pUser.Profile Is Nothing AndAlso pUser.Profile.Id.HasValue Then
                wSqlCmd.Parameters("@Id_Perfil").Value = pUser.Profile.Id.Value
            End If
            If pUser.DocumentNumber.HasValue Then
                wSqlCmd.Parameters("@NroDocumento").Value = pUser.DocumentNumber.Value
            End If
            wSqlCmd.Parameters("@UsuriosDadosBaja").Value = Convert.ToByte(pNotActiveUsers)

            wSqlConn.Open()

            wUserList = New List(Of UserBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read()
                wUserBE = New UserBE

                wUserBE.UserName = Convert.ToString(wReader("NombreUsuario"))
                wUserBE.Password = Convert.ToString(wReader("Password"))
                wUserBE.Name = Convert.ToString(wReader("Nombre"))
                wUserBE.Answer = Convert.ToString(wReader("Respuesta"))
                wUserBE.Blocked = Convert.ToBoolean(wReader("Bloqueado"))
                wUserBE.DocumentNumber = Convert.ToInt32(wReader("NroDocumento"))
                If Not wReader("CodPostal") Is DBNull.Value Then
                    wUserBE.PostalCode = Convert.ToString(wReader("CodPostal"))
                End If
                If Not wReader("Direccion") Is DBNull.Value Then
                    wUserBE.Direction = Convert.ToString(wReader("Direccion"))
                End If
                If Not wReader("Celular") Is DBNull.Value Then
                    wUserBE.Celphone = Convert.ToString(wReader("Celular"))
                End If
                If Not wReader("Telefono") Is DBNull.Value Then
                    wUserBE.Phone = Convert.ToString(wReader("Telefono"))
                End If
                If Not wReader("Email") Is DBNull.Value Then
                    wUserBE.Email = Convert.ToString(wReader("Email"))
                End If
                If Not wReader("BlockStart") Is DBNull.Value Then
                    wUserBE.BlockStarted = Convert.ToDateTime(wReader("BlockStart"))
                End If
                If Not wReader("FechaBaja") Is DBNull.Value Then
                    wUserBE.EndDate = Convert.ToDateTime(wReader("FechaBaja"))
                End If
                If Not wReader("CUIT") Is DBNull.Value Then
                    wUserBE.Cuit = Convert.ToString(wReader("CUIT"))
                End If
                'Tipo de Documento
                wUserBE.DocumentType = New DocumentTypeBE
                wUserBE.DocumentType.Id = Convert.ToInt32(wReader("Tipo_Doc"))
                wUserBE.DocumentType.Description = Convert.ToString(wReader("TipoDocumento"))
                'Perfil
                wUserBE.Profile = New ProfileBE
                wUserBE.Profile.Id = Convert.ToInt32(wReader("Id_Perfil"))
                wUserBE.Profile.Description = Convert.ToString(wReader("Perfil"))
                'Pregunta
                wUserBE.Question = New QuestionBE
                wUserBE.Question.Id = Convert.ToInt32(wReader("ID_Pregunta"))
                wUserBE.Question.Description = Convert.ToString(wReader("Pregunta"))
                'País
                If Not wReader("Id_Pais") Is DBNull.Value Then
                    wUserBE.Country = New CountryBE
                    wUserBE.Country.Id = Convert.ToInt32(wReader("Id_Pais"))
                    wUserBE.Country.Description = Convert.ToString(wReader("Pais"))
                End If
                'Provincia
                If Not wReader("Id_Provincia") Is DBNull.Value Then
                    wUserBE.Province = New ProvinceBE
                    wUserBE.Province.Id = Convert.ToInt32(wReader("Id_Provincia"))
                    wUserBE.Province.Description = Convert.ToString(wReader("Provincia"))
                End If
                'Ciudad
                If Not wReader("Id_Ciudad") Is DBNull.Value Then
                    wUserBE.City = New CityBE
                    wUserBE.City.Id = Convert.ToInt32(wReader("Id_Ciudad"))
                    wUserBE.City.Description = Convert.ToString(wReader("Ciudad"))
                End If
                'Motivo de Baja
                If Not wReader("MotivoBaja") Is DBNull.Value Then
                    wUserBE.AccountCloseMotive = Convert.ToString(wReader("MotivoBaja"))
                End If
                'Sexo
                wUserBE.Sex = New SexBE
                wUserBE.Sex.Id = Convert.ToInt32(wReader("Id_Sexo"))
                wUserBE.Sex.Description = Convert.ToString(wReader("Sexo"))


                wUserList.Add(wUserBE)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wUserList
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Sub Insert(pUser As UserBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Usuarios_i"

            wSqlCmd.Parameters.Add("@NombreUsuario", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 150)
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@NroDocumento", SqlDbType.Decimal)
            wSqlCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Perfil", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@ID_Pregunta", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Respuesta", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Sexo", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@CodPostal", SqlDbType.NVarChar, 10)
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Telefono", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Celular", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 50)

            wSqlCmd.Parameters("@NombreUsuario").Value = pUser.UserName
            wSqlCmd.Parameters("@Nombre").Value = pUser.Name
            wSqlCmd.Parameters("@Tipo_Doc").Value = pUser.DocumentType.Id
            wSqlCmd.Parameters("@NroDocumento").Value = pUser.DocumentNumber
            wSqlCmd.Parameters("@Password").Value = pUser.Password
            wSqlCmd.Parameters("@Id_Perfil").Value = pUser.Profile.Id
            wSqlCmd.Parameters("@ID_Pregunta").Value = pUser.Question.Id
            wSqlCmd.Parameters("@Respuesta").Value = pUser.Answer
            wSqlCmd.Parameters("@Id_Sexo").Value = pUser.Sex.Id
            If Not pUser.City Is Nothing Then
                wSqlCmd.Parameters("@Id_Ciudad").Value = pUser.City.Id
            End If
            If Not pUser.Province Is Nothing Then
                wSqlCmd.Parameters("@Id_Provincia").Value = pUser.Province.Id
            End If
            If Not pUser.Country Is Nothing Then
                wSqlCmd.Parameters("@Id_Pais").Value = pUser.Country.Id
            End If
            wSqlCmd.Parameters("@CodPostal").Value = pUser.PostalCode
            wSqlCmd.Parameters("@Direccion").Value = pUser.Direction
            wSqlCmd.Parameters("@Telefono").Value = pUser.Phone
            wSqlCmd.Parameters("@Celular").Value = pUser.Celphone
            wSqlCmd.Parameters("@Email").Value = pUser.Email
            wSqlCmd.Parameters("@CUIT").Value = pUser.Cuit

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(pUser As UserBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Usuarios_u"

            wSqlCmd.Parameters.Add("@NombreUsuario", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 150)
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@NroDocumento", SqlDbType.Decimal)
            wSqlCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Perfil", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@ID_Pregunta", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Respuesta", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Sexo", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@CodPostal", SqlDbType.NVarChar, 10)
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Telefono", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Celular", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@FechaBaja", SqlDbType.DateTime)
            wSqlCmd.Parameters.Add("@MotivoBaja", SqlDbType.NVarChar, 500)

            wSqlCmd.Parameters("@NombreUsuario").Value = pUser.UserName
            wSqlCmd.Parameters("@Nombre").Value = pUser.Name
            wSqlCmd.Parameters("@Tipo_Doc").Value = pUser.DocumentType.Id
            wSqlCmd.Parameters("@NroDocumento").Value = pUser.DocumentNumber
            wSqlCmd.Parameters("@Password").Value = pUser.Password
            wSqlCmd.Parameters("@Id_Perfil").Value = pUser.Profile.Id
            wSqlCmd.Parameters("@ID_Pregunta").Value = pUser.Question.Id
            wSqlCmd.Parameters("@Respuesta").Value = pUser.Answer
            wSqlCmd.Parameters("@Id_Sexo").Value = pUser.Sex.Id
            If Not pUser.City Is Nothing Then
                wSqlCmd.Parameters("@Id_Ciudad").Value = pUser.City.Id
            End If
            If Not pUser.Province Is Nothing Then
                wSqlCmd.Parameters("@Id_Provincia").Value = pUser.Province.Id
            End If
            If Not pUser.Country Is Nothing Then
                wSqlCmd.Parameters("@Id_Pais").Value = pUser.Country.Id
            End If
            wSqlCmd.Parameters("@CodPostal").Value = pUser.PostalCode
            wSqlCmd.Parameters("@Direccion").Value = pUser.Direction
            wSqlCmd.Parameters("@Telefono").Value = pUser.Phone
            wSqlCmd.Parameters("@Celular").Value = pUser.Celphone
            wSqlCmd.Parameters("@Email").Value = pUser.Email
            wSqlCmd.Parameters("@CUIT").Value = pUser.Cuit
            wSqlCmd.Parameters("@FechaBaja").Value = pUser.EndDate
            If Not String.IsNullOrEmpty(pUser.AccountCloseMotive) Then
                wSqlCmd.Parameters("@MotivoBaja").Value = pUser.AccountCloseMotive
            End If

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

    '#Region "<< -DeleteBatch- >>"
    '    Public Shared Sub DeleteBatch(pUserNameList As List(Of String))
    '        Dim wSqlConn As SqlConnection = Nothing
    '        Dim wSqlCmd As SqlCommand = Nothing
    '        Dim wConnStr As String = String.Empty
    '        Dim wStr As System.Text.StringBuilder = Nothing

    '        Try
    '            'No hay nada que hacer
    '            If pUserNameList.Count = 0 Then Return

    '            'Obtengo la cadena de conexión
    '            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
    '            'Creo la conexión a la BD
    '            wSqlConn = New SqlConnection(wConnStr)
    '            'Creo el comando
    '            wSqlCmd = New SqlCommand()
    '            wSqlCmd.Connection = wSqlConn
    '            wSqlCmd.CommandType = CommandType.Text

    '            wStr = New System.Text.StringBuilder
    '            For Each wUserName As String In pUserNameList
    '                wStr.Append("EXEC Usuarios_d")
    '                wStr.Append(String.Format(" @NombreUsuario = {0} ", wUserName))
    '                wStr.Append(vbNewLine)
    '            Next

    '            wSqlCmd.CommandText = wStr.ToString()

    '            wSqlConn.Open()
    '            wSqlCmd.ExecuteNonQuery()
    '            wSqlConn.Close()
    '        Catch ex As Exception
    '            Throw ex
    '        End Try
    '    End Sub
    '#End Region

End Class
